from tkinter import *
from tkinter import ttk, filedialog
from openpyxl.utils import column_index_from_string
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
import xlsxwriter
import threading
from pathlib import Path
from io import BytesIO
from PIL import Image
import requests
import re


class ExcelProcessorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel图片批量处理工具")
        self.root.geometry("600x400")
        self.mainframe = ttk.Frame(root, padding="3 3 12 12")
        self.mainframe.grid(column=0, row=0, sticky=(N, W, E, S))
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)

        # 创建一个选择xlsx文件目录的输入框
        self.selectedExcelFilePath = StringVar()
        self.selectedExcelFilePath_entry = ttk.Entry(
            self.mainframe, width=50, textvariable=self.selectedExcelFilePath
        )
        self.selectedExcelFilePath_entry.grid(column=2, row=1, sticky=(W, E))
        self.selectedExcelFilePath.set("未选择文件")
        ttk.Label(self.mainframe, text="路径：").grid(column=1, row=1, sticky=W)
        ttk.Button(self.mainframe, text="选择xlsx文件", command=self.select_file).grid(
            column=3, row=1, sticky=W
        )

        # 列输入部分
        self.userExcelColumnLetter = StringVar()
        self.userExcelColumnLetter_entry = ttk.Entry(
            self.mainframe, width=30, textvariable=self.userExcelColumnLetter
        )
        self.userExcelColumnLetter_entry.grid(column=2, row=2, sticky=W)
        ttk.Label(self.mainframe, text="输入图片列字母\n(用空格分隔多个列)：").grid(
            column=1, row=2, sticky=W
        )

        # 进度条
        ttk.Label(self.mainframe, text="处理进度:").grid(row=3, column=1, sticky="w")
        self.progress = ttk.Progressbar(self.mainframe, length=400, mode="determinate")
        self.progress.grid(row=3, column=2, columnspan=2, sticky="ew")

        # 按钮框架
        self.button_frame = ttk.Frame(self.mainframe)
        self.button_frame.grid(row=5, column=2)

        self.convert_btn = ttk.Button(
            self.button_frame, text="转换URL", command=self.start_convert
        )
        self.convert_btn.grid(row=6, column=2)

        self.check_btn = ttk.Button(
            self.button_frame, text="检查URL", command=self.start_check
        )
        self.check_btn.grid(row=7, column=2)

        self.cancel_btn = ttk.Button(
            self.button_frame, text="关闭", command=self.root.quit
        )
        self.cancel_btn.grid(row=8, column=2)

        # 状态标签
        self.status_var = StringVar(value="就绪")
        ttk.Label(self.mainframe, textvariable=self.status_var).grid(row=4, column=2)

        for child in self.mainframe.winfo_children():
            child.grid_configure(padx=5, pady=5)

    def update_progress(self, current, total):
        progress_value = (current / total) * 100
        self.progress["value"] = progress_value
        self.root.update_idletasks()

    def set_buttons_state(self, state):
        self.convert_btn["state"] = state
        self.check_btn["state"] = state
        self.cancel_btn["state"] = state

    def select_file(self):
        file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
        if file_path:
            self.selectedExcelFilePath.set(file_path)

    def start_convert(self):
        if (
            not self.selectedExcelFilePath.get()
            or self.selectedExcelFilePath.get() == "未选择文件"
        ):
            self.status_var.set("错误：请先选择Excel文件")
            return
        if not self.userExcelColumnLetter.get():
            self.status_var.set("错误：请输入列字母")
            return

        self.set_buttons_state("disabled")
        self.status_var.set("正在转换URL...")
        threading.Thread(target=self.convert_process, daemon=True).start()

    def convert_process(self):
        file_path = Path(self.selectedExcelFilePath.get())
        imgcol_lst_str = self.userExcelColumnLetter.get().upper().strip().split()
        imgcol_lst = [column_index_from_string(num) for num in imgcol_lst_str]
        try:
            self.image_insert(file_path, imgcol_lst)
            self.status_var.set("转换完成！")
        except Exception as e:
            self.status_var.set(f"转换失败：{str(e)}")
        finally:
            self.set_buttons_state("normal")
            self.progress["value"] = 100

    def image_insert(self, file_path, col_list):
        source_wb = load_workbook(filename=file_path)
        source_ws = source_wb.active
        source_total_rows = source_ws.max_row
        source_total_range = (source_total_rows - 1) * len(col_list)

        target_wb = xlsxwriter.Workbook(f"{file_path.as_posix()}_out.xlsx")
        target_ws = target_wb.add_worksheet("result")

        input_xlsx_column_list = list(sorted(col_list))

        rows_written_count = 0  # 这是记录写入行数的计数器，可重复使用。
        i = 0  # 用于进度更新
        for row in source_ws.iter_rows(
            min_row=1,
            max_row=source_total_rows,
            min_col=1,
            max_col=6,
            values_only=True,
        ):
            for col_num, data in enumerate(row):
                target_ws.write(rows_written_count, col_num, data)
            rows_written_count += 1

        columns_written_count = (
            6  # 这是记录写入列数的计数器，可重复使用。worksheet.write写入从0开始
        )

        url_len_max = 1
        for input_column in input_xlsx_column_list:
            rows_written_count = 1  # 重置计数器以便重新使用
            target_ws.write(
                0,
                columns_written_count,
                source_ws.cell(row=1, column=input_column).value,
            )
            for row in source_ws.iter_rows(
                min_row=2,
                max_row=source_total_rows,
                min_col=input_column,
                max_col=input_column,
                values_only=True,
            ):
                i += 1
                if row[0] is None:
                    continue
                else:
                    # 获取当前列的图片URL列表
                    # img_url_list = row[0].split("，")
                    img_url_list = [url.strip() for url in re.split(r"[，;]", row[0])]
                    if url_len_max < len(img_url_list):
                        url_len_max = len(img_url_list)
                    k = 0  # 用于在同一行中写入多张图片
                    y_size_variable = 0
                    for img_url in img_url_list:
                        # 检查图片URL是否以有效图片后缀结束
                        img_url_lower = img_url.lower()
                        try:
                            if any(
                                ext in img_url_lower
                                for ext in (".jpg", ".jpeg", ".png", ".gif")
                            ):
                                response = requests.get(img_url)
                                if response.status_code == 200:
                                    # 打开一个文件以二进制写入模式
                                    image_data = BytesIO(response.content)
                                    im = Image.open(image_data)
                                    x_size, y_size = im.size
                                    target_ws.insert_image(
                                        rows_written_count,
                                        columns_written_count + k,
                                        img_url,
                                        options={
                                            "image_data": image_data,
                                            "x_scale": 100.0 / x_size,
                                            "y_scale": 100.0 / x_size,
                                        },
                                    )
                                    if (100.0 / x_size) * y_size > y_size_variable:
                                        y_size_variable = (100.0 / x_size) * y_size
                                else:
                                    print(f"下载失败，状态码: {response.status_code}")

                            elif ".webp" in img_url_lower:
                                # image_data = BytesIO(urlopen(img_url).read())
                                response = requests.get(img_url)
                                if response.status_code == 200:
                                    with Image.open(image_data) as webp_img:
                                        png_buffer = BytesIO()
                                        webp_img.save(png_buffer, format="PNG")
                                        x_size, y_size = webp_img.size
                                        target_ws.insert_image(
                                            rows_written_count,
                                            columns_written_count + k,
                                            img_url,
                                            options={
                                                "image_data": png_buffer,
                                                "x_scale": 100.0 / x_size,
                                                "y_scale": 100.0 / x_size,
                                            },
                                        )
                                    if (100.0 / x_size) * y_size > y_size_variable:
                                        y_size_variable = (100.0 / x_size) * y_size
                                else:
                                    print(f"下载失败，状态码: {response.status_code}")
                            else:
                                continue
                            k += 1
                        except Exception as e:
                            print(e)
                            k += 1
                            continue
                target_ws.set_row(rows_written_count, y_size_variable)
                rows_written_count += 1
                self.update_progress(i - 1, source_total_range)
            target_ws.set_column(
                columns_written_count, columns_written_count + url_len_max, 20
            )
            columns_written_count += url_len_max

        target_wb.close()

    def start_check(self):
        if (
            not self.selectedExcelFilePath.get()
            or self.selectedExcelFilePath.get() == "未选择文件"
        ):
            self.status_var.set("错误：请先选择Excel文件")
            return
        if not self.userExcelColumnLetter.get():
            self.status_var.set("错误：请输入列字母")
            return

        self.set_buttons_state("disabled")
        self.status_var.set("正在检查URL...")
        threading.Thread(target=self.check_process, daemon=True).start()

    def check_process(self):
        file_path = Path(self.selectedExcelFilePath.get())
        imgcol_lst_str = self.userExcelColumnLetter.get().upper().strip().split()
        imgcol_lst = [column_index_from_string(num) for num in imgcol_lst_str]

        try:
            for col in imgcol_lst:
                self.fill_color_in_cell(file_path, col)
            self.status_var.set("检查完成！")
        except Exception as e:
            self.status_var.set(f"检查失败：{str(e)}")
        finally:
            self.set_buttons_state("normal")
            self.progress["value"] = 100

    def fill_color_in_cell(self, file_path, imgcol):
        wb = load_workbook(file_path)
        ws = wb.worksheets[0]
        valid_extensions = (".jpg", ".gif", ".png", ".jpeg")
        total_rows = ws.max_row - 1

        for index, row in enumerate(
            ws.iter_rows(
                min_col=imgcol,
                max_col=imgcol,
                min_row=2,
                max_row=ws.max_row,
                values_only=True,
            )
        ):
            if row[0] is None:
                continue
            cell_value_list = row[0].split("，")
            for cell_value in cell_value_list:
                if not cell_value.endswith(valid_extensions):
                    ws.cell(row=index + 2, column=imgcol).fill = PatternFill(
                        start_color="FF0000", end_color="FF0000", fill_type="solid"
                    )
            self.update_progress(index + 1, total_rows)
        wb.save(file_path)


if __name__ == "__main__":
    root = Tk()
    app = ExcelProcessorApp(root)
    root.mainloop()